package com.supermap.dataservice.cloud.agrinfo.crop;

import com.supermap.dataservice.util.JSONUtil;
import org.apache.ibatis.annotations.Mapper;
import org.apache.ibatis.annotations.Param;
import org.apache.ibatis.annotations.Select;

import java.util.List;
import java.util.Map;

/**
 * @author aqua
 * @description 作物品种
 * @date 2018年11月08 下午 4:36
 * @email 1301584033@qq.com
 */
@Mapper
public interface CropMapper {
    /**
     * 获取所有品种列表
     * */
    @Select("select id as codeId,name as codeName from t_crop where level=1")
    List<Map<String,Object>> getListCrop();

    /**
     * 获取所有品种下面的种类列表
     * */
    @Select("select id as codeId,name as codeName  from t_crop t where t.parentid=#{codeId}")
    List<Map<String,Object>> getListCropChildNode(@Param("codeId")String codeId);


    @Select("SELECT e.id AS expertId, c.id AS cropId, c.code, c.name, c.description, DATE_FORMAT(c.createdate,'%Y-%m-%d %H:%i:%s') AS creattime, c.img_ico\n" +
            "FROM t_expertinfo e\n" +
            "LEFT JOIN t_expert_subject eu ON e.id = eu.expertId\n" +
            "LEFT JOIN t_subject_crops uc ON eu.subjectId = uc.subject\n" +
            "LEFT JOIN t_crop c ON uc.crop = c.id\n" +
            "WHERE e.userid = #{expertUserId}\n" +
            "GROUP BY e.id")
    List<Map<String , Object>> getListCropByExpertUserId(@Param("expertUserId") Integer expertUserId);


    @Select("select t.subject from t_subject_crops  t where t.crop = #{crop}")
    int getSubjectByCrop(@Param("crop") Integer crop);


}
